﻿/*===============================================================================
VSLT_LoadTestIntervals

This table contains all of the time and count information for any constant
intervals that are in the test run. The data is populated by the reporting
tool.
===============================================================================*/
	--START CODE--
	CREATE TABLE VSLT_LoadTestIntervals (
		[LoadTestRunId] [int] NOT NULL,
		[StartOffset] [int] NOT NULL,
		[EndOffset] [int] NOT NULL,
		[UserLoad] [int] NOT NULL,
		[NumberOfDataPoints] [int] NOT NULL,
	PRIMARY KEY CLUSTERED
	(
		[LoadTestRunId] ASC,
		[StartOffset] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
/*===============================================================================
VSLT_LoadTestCoreInfo

This table contains all of the data (not already in the LoadTestRun table) that 
is used for creating the Summary Report. It is either calculated at the end of
the run (for all tests executed after the tool is installed) or during tool
install and configuration.
===============================================================================*/
	--START CODE--
	CREATE TABLE VSLT_LoadTestCoreInfo(
		LoadTestRunId	INT NOT NULL
		,AgentCount		INT NULL
		,MaxUsers		INT NULL
		,SampleInterval INT NULL
	
		,TotalTests		INT NULL
		,PassedTests	INT NULL
		,FailedTests	INT NULL
		,AvgTT_All		FLOAT NULL			--Avg Test Time 
		,TestsSec_All	FLOAT NULL

		,TotalTrans		INT NULL			
		,AvgTrET_All	FLOAT NULL			--Avg Trans Elapsed Time
		,AvgTrRT_All	FLOAT NULL			--Avg Trans Response Time
		,TransSec_All	FLOAT NULL			--Transactions Per Second - All

		,TotalPages		INT NULL			
		,PagesSec_All	FLOAT NULL			--PagesPerSec - All
		,AvgPgT_All		FLOAT NULL			--Avg Page Time - All
		
		,TotalRequests	INT NULL
		,PassedRequests	INT NULL
		,FailedRequests	INT NULL
		,CachedRequests	INT NULL			--Number of cached requests
		,ReqSec_All		FLOAT NULL			--Requests Per Sec - All
		,AvgReqT_All	FLOAT NULL			--Avg Request Time - All
		,AvgReqLen_All	FLOAT NULL			--Avg Request Content Length - All

		,AvgTT_Pass			FLOAT NULL			
		,TestsSec_Pass		FLOAT NULL
		,AvgTrET_Pass		FLOAT NULL			
		,AvgTrRT_Pass		FLOAT NULL			
		,TransSec_Pass		FLOAT NULL			
		,PassedPages		INT NULL			
		,PagesSec_Pass		FLOAT NULL			
		,AvgPgT_Pass		FLOAT NULL			
	
		,PctTime_WebTest		FLOAT NULL		--Percent of time (by the agents ) spent in the web test code
		,PctTime_Rules			FLOAT NULL		--Percent of time (by the agents ) spent in Rules and Plugins
		,PctTime_LoadPlugin		FLOAT NULL		--Percent of time (by the agents ) spent in any Load Test Plugins

		,FLAG_ContainsPages				bit NULL
		,FLAG_ContainsTransactions		bit NULL
		,FLAG_ContainsDetailedTimingInfo	bit NULL
		,FLAG_ContainsStepLoadProfile	bit NULL
		,FLAG_TestAborted				bit NULL
		,FLAG_LoadTestParsingFailed		bit NULL
		,FLAG_LoadTestIntervalProfile	INT NULL
	
	PRIMARY KEY CLUSTERED 
		(
			LoadTestRunId ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
/*===============================================================================
VSLT_ScoreCardMachineCPU

This table contains all of the summary perfmon values for CPU Utilization for 
each machine in the test run results. It is used to quickly build scorecard 
charts for run comparisons.
===============================================================================*/
	--START CODE--
	CREATE TABLE VSLT_ScoreCardMachineCPU (
		[LoadTestRunId] [int] NOT NULL,
		[MachineName] [nvarchar](255) NOT NULL,
		[MachineTypeId] [int] NOT NULL,
		[InstanceId] [int] NOT NULL,
		[Qty] [real] NULL,
		[Min] [real] NULL,
		[Avg] [real] NULL,
		[Max] [real] NULL,
	PRIMARY KEY CLUSTERED 
	(
		[LoadTestRunId] ASC,
		[MachineName] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
/*===============================================================================
VSLT_ScoreCardMachineAvailableMB

This table contains all of the summary perfmon values for Available MBytes for 
each machine in the test run results. It is used to quickly build scorecard 
charts for run comparisons.
===============================================================================*/
	--START CODE--
	CREATE TABLE VSLT_ScoreCardMachineAvailableMB (
		[LoadTestRunId] [int] NOT NULL,
		[MachineName] [nvarchar](255) NOT NULL,
		[MachineTypeId] [int] NOT NULL,
		[InstanceId] [int] NOT NULL,
		[Qty] [real] NULL,
		[Min] [real] NULL,
		[Avg] [real] NULL,
		[Max] [real] NULL,
	PRIMARY KEY CLUSTERED 
	(
		[LoadTestRunId] ASC,
		[MachineName] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
/*===============================================================================
VSLT_ScoreCardMachineType

This table contains all of the possible types of machines that you are collecting
data for. It allows you to average out or trend values based on the role that the
machine plays in the SUT.
===============================================================================*/
	--START CODE--
	CREATE TABLE VSLT_ScoreCardMachineType (
		[MachineTypeId] [int] NOT NULL,
		[MachineTypeDescription] [nvarchar](1024) NOT NULL
	)
	GO
/*===============================================================================
TSL_DbChangeLogTypes

This table contains the list of stored procedures that make changes to the DB
and is used to track the owner of any record in the TSL_DbChangeLogs table.
===============================================================================*/
	--START CODE--
	CREATE TABLE VSLT_IntervalProfileTypes(
		[ProfileTypeId] [int] NOT NULL,
		[ProfileTypeDescription] [nvarchar](1024) NOT NULL,
	)
	GO
/*===============================================================================
TSL_DbChangeLogs

This table tracks any changes that are made to the results DB by the Reporting
Tool. That way we can track what the reporting tool is doing.
===============================================================================*/
	--START CODE--
	IF NOT EXISTS  -- TSL_DbChangeLogs
		(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_DbChangeLogs') AND type in (N'U'))
		CREATE TABLE TSL_DbChangeLogs (
			[LoadTestRunId] [int] NOT NULL,
			[LogId] [int] IDENTITY(1,1) NOT NULL,
			[ChangeTypeId] [int] NOT NULL,
			[ChangeTime] [datetime] NOT NULL,
			[LogComment] [nvarchar](1024) NULL,
		PRIMARY KEY CLUSTERED 
		(
			[LogId] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]
	GO
/*===============================================================================
TSL_DbChangeLogTypes

This table contains the list of stored procedures that make changes to the DB
and is used to track the owner of any record in the TSL_DbChangeLogs table.
===============================================================================*/
	--START CODE--
	CREATE TABLE TSL_DbChangeLogTypes(
		[ChangeTypeId] [int] NOT NULL,
		[ChangeTypeDescription] [nvarchar](1024) NOT NULL,
	)
	GO



/*===============================================================================
Old Name: VSLT_View_PerfmonInstanceNamesAndIds

LoadTestRunId	InstanceId	InstanceName									MachineName		CategoryName	CounterName			Instance
11				0			\\CONTROLLER\Memory\Page Faults/sec				CONTROLLER		Memory			Page Faults/sec	
11				390			\\CONTROLLER\Process(QTAgent32)\Thread Count	CONTROLLER		Process			Thread Count		QTAgent32
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_PerfmonInstanceNamesAndIds AS
	---------------------------------------------------------------------------------
		SELECT 
			instance.LoadTestRunId
			,instance.InstanceId
			,(
				'\\' + category.MachineName
				+ '\' + category.CategoryName
				+	case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'
							then ''
							else '(' + instance.InstanceName  + ')'
							end
				+ '\' + counter.CounterName
			) AS InstanceName
			,category.MachineName
			,category.CategoryName
			,counter.CounterName
			,(case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'
							then ''
							else instance.InstanceName
							end) AS Instance
			,instance.LoadTestItemId

		FROM LoadTestPerformanceCounterCategory AS category 

		INNER JOIN LoadTestPerformanceCounter AS counter 
			ON category.LoadTestRunId = counter.LoadTestRunId
			AND category.CounterCategoryId = counter.CounterCategoryId

		INNER JOIN LoadTestPerformanceCounterInstance AS instance 
			ON counter.CounterId = instance.CounterId
			AND counter.LoadTestRunId = instance.LoadTestRunId
	GO
/*===============================================================================
VSLT_View_PageSummary
Pulls data from the LoadTestPageSummaryData table and shows it with the proper name resolutions
NOTE: This data is only accurate for runs where the user load remained constant throughout the load.

LoadTestRunId	PageId	TestCaseId	ScenarioId	ScenarioName	TestCaseName		RequestUri		PageCount	Average	Minimum	Maximum	Percentile90	Percentile95	Percentile99	Median	StandardDeviation
1				7		0			0			Scenario1		PhoeneticAlphabet	http://usmi..	12			3.28667	1.066	3.826	3.793			3.826			3.826			3.705	0.959920424942725
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_PageSummary AS
	SELECT 
		pageSummaryData.LoadTestRunId
		,pageSummaryData.PageId
		,testCase.TestCaseId
		,scenario.ScenarioId
		,scenario.ScenarioName
		,testCase.TestCaseName
		,requestMap.RequestUri
		, pageSummaryData.PageCount
		, pageSummaryData.Average
		, pageSummaryData.Minimum
		, pageSummaryData.Maximum
		, pageSummaryData.Percentile90
		, pageSummaryData.Percentile95
		, pageSummaryData.Percentile99
		, pageSummaryData.Median
		, pageSummaryData.StandardDeviation 
		, run.RunDuration
	FROM LoadTestPageSummaryData AS pageSummaryData

	INNER JOIN WebLoadTestRequestMap AS requestMap 
		ON pageSummaryData.LoadTestRunId = requestMap.LoadTestRunId
		AND pageSummaryData.PageId = requestMap.RequestId

	INNER JOIN LoadTestCase AS testCase 
		ON pageSummaryData.LoadTestRunId = testCase.LoadTestRunId 
		AND requestMap.TestCaseId = testCase.TestCaseId

	INNER JOIN LoadTestScenario AS scenario 
		ON pageSummaryData.LoadTestRunId = scenario.LoadTestRunId 
		AND testCase.ScenarioId = scenario.ScenarioId

	INNER JOIN LoadTestRun as run
		ON pageSummaryData.LoadTestRunId = run.LoadTestRunId
	GO
/*===============================================================================

LoadTestRunId	ScenarioId	ScenarioName	TestCaseId	TestCaseName				RequestId	RequestUri				RequestNameWithId
11				0			Scenario1		0			Single Project_ViewAll_OFF	18			ReportServer-Homepage1	ReportServer-Homepage1(018)
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_PageNamesWithIDs AS
		SELECT a.LoadTestRunId
			, c.ScenarioId
			, ScenarioName
			, a.TestCaseId
			, TestCaseName
			, RequestId
			, RequestUri 
			,RequestUri + '(' 
				+ RIGHT ('000'
				+ CAST(RequestId AS nvarchar(3)), 3) 
				+ ')' AS RequestNameWithId
		FROM dbo.WebLoadTestRequestMap AS a

		INNER JOIN LoadTestCase AS b 
			ON a.LoadTestRunId = b.LoadTestRunId 
			AND a.TestCaseId = b.TestCaseId

		INNER JOIN LoadTestScenario AS c 
			ON b.LoadTestRunId = c.LoadTestRunId 
			AND b.ScenarioId = c.ScenarioId
	GO
/*===============================================================================
VSLT_View_TestNamesWithIDs

LoadTestRunId	ScenarioId	ScenarioName	TestCaseId	TestCaseName				TestNameWithId
11				0			Scenario1		0			Single Project_ViewAll_OFF	Single Project_ViewAll_OFF(000)
12				0			Scenario1		0			Single Project_ViewAll_OFF	Single Project_ViewAll_OFF(000)
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_TestNamesWithIDs AS

		SELECT a.LoadTestRunId, c.ScenarioId, ScenarioName, a.TestCaseId, TestCaseName 
			  ,TestCaseName + '(' + RIGHT ('000'+ CAST(TestCaseId AS nvarchar(3)), 3) + ')' AS TestNameWithId
		FROM LoadTestCase AS a

		INNER JOIN LoadTestScenario AS c 
			ON a.LoadTestRunId = c.LoadTestRunId AND a.ScenarioId = c.ScenarioId
	GO
/*===============================================================================
VSLT_View_TransactionNamesWithIDs

LoadTestRunId	ScenarioId	ScenarioName	TestCaseId	TestCaseName				TransactionId	TransactionName	TransactionNameWithId
11				0			Scenario1		0			Single Project_ViewAll_OFF	0				HomePage		HomePage(000)
11				0			Scenario1		0			Single Project_ViewAll_OFF	1				Expand1			Expand1(001)
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_TransactionNamesWithIDs AS

		SELECT a.LoadTestRunId, c.ScenarioId, ScenarioName, a.TestCaseId, TestCaseName, TransactionId, TransactionName 
			  ,[TransactionName] + '(' + RIGHT ('000'+ CAST([TransactionId] AS nvarchar(3)), 3) + ')' AS TransactionNameWithId
		FROM [WebLoadTestTransaction] AS a

		INNER JOIN LoadTestCase AS b 
			ON a.LoadTestRunId = b.LoadTestRunId AND a.TestCaseId = b.TestCaseId

		INNER JOIN LoadTestScenario AS c 
			ON b.LoadTestRunId = c.LoadTestRunId AND b.ScenarioId = c.ScenarioId
	GO
/*===============================================================================
VSLT_View_LoadTestSummary

This view provides a single list of all items from the LoadTestRun and
TSL_LoadTestCoreInfo tables. This view is where the Reporting Tool gets
the bulk of the Summary Data it provides
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_LoadTestSummary AS
		SELECT a.LoadTestRunId
			  ,LoadTestName
			  ,Description
			  ,StartTime
			  ,EndTime
			  ,RunDuration
			  ,WarmupTime
			  ,RunSettingUsed
			  ,IsLocalRun
			  ,ControllerName
			  ,Outcome
			  ,CooldownTime
			  ,AgentCount
			  ,MaxUsers
			  ,SampleInterval
			  ,TotalTests
			  ,PassedTests
			  ,FailedTests
			  ,AvgTT_All
			  ,TestsSec_All
			  ,TotalTrans
			  ,AvgTrET_All
			  ,AvgTrRT_All
			  ,TransSec_All
			  ,TotalPages
			  ,PagesSec_All
			  ,AvgPgT_All
			  ,TotalRequests
			  ,PassedRequests
			  ,FailedRequests
			  ,CachedRequests
			  ,ReqSec_All
			  ,AvgReqT_All
			  ,AvgReqLen_All
			  ,AvgTT_Pass
			  ,TestsSec_Pass
			  ,AvgTrET_Pass
			  ,AvgTrRT_Pass
			  ,TransSec_Pass
			  ,PassedPages
			  ,PagesSec_Pass
			  ,AvgPgT_Pass
			  ,PctTime_WebTest
			  ,PctTime_Rules
			  ,PctTime_LoadPlugin
			  ,FLAG_ContainsPages
			  ,FLAG_ContainsTransactions
			  ,FLAG_ContainsDetailedTimingInfo
			  ,FLAG_ContainsStepLoadProfile
			  ,FLAG_TestAborted
			  ,FLAG_LoadTestParsingFailed
		      
		  FROM VSLT_LoadTestCoreInfo AS a
		INNER JOIN LoadTestRun AS b ON a.LoadTestRunId = b.LoadTestRunId  		
	GO



/*==================================================================
* Returns the version of the TSL stored procedure set that is currently
* installed in the repository. This is used to see if the latest version
* of the procedures is installed.
===============================================================================*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Get_TSL_Reporting_Version
	AS
	BEGIN
		DECLARE @Version nvarchar(25)
		----- SET THE VERSION NUMBER HERE -----
		----- This should match exactly the version number in the reporting tool's config file
		SET @Version = '6.0.0.1 Beta'
		SELECT @Version
	END
	GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_UpdateScoreCardMachineAvailableMBTable
	AS	
	BEGIN
		---------------------------------------------------------------------------------
		-- Executed automatically by VSLT_prc_UpdateLoadTestCoreInfoTable
		---------------------------------------------------------------------------------
		DECLARE @RunId INT
		DECLARE myCursor CURSOR FOR
			SELECT LoadTestRunId FROM LoadTestRun 
			WHERE LoadTestRunId NOT IN (SELECT DISTINCT LoadTestRunId FROM VSLT_ScoreCardMachineAvailableMB)
			AND Outcome != 'In Progress'
			
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @RunId
		WHILE @@FETCH_STATUS = 0
			BEGIN	
				---------------------------------------------------------------------------------
				-- Core Summary Info
				---------------------------------------------------------------------------------
				INSERT INTO VSLT_ScoreCardMachineAvailableMB
					SELECT 
						a.LoadTestRunId 
						,a.MachineName
						,0	-- NOTE: Setting MachineType to 'undefined'. Add your own code to determine what type the machine is
						,b.InstanceId
						,COUNT(a.ComputedValue) AS [Qty]
						,CAST(MIN(a.ComputedValue) AS Decimal(15,2)) AS [Min]
						,CAST(AVG(a.ComputedValue) AS Decimal(15,2)) AS [Avg]
						,CAST(MAX(a.ComputedValue) AS Decimal(15,2)) AS [Max]
					FROM LoadTestComputedCounterSample AS a
					INNER JOIN VSLT_View_PerfmonInstanceNamesAndIds AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
						AND a.MachineName = b.MachineName 
						AND a.CategoryName = b.CategoryName
						AND a.CounterName = b.CounterName 
						WHERE a.CategoryName = 'Memory' 
						AND a.CounterName = 'Available MBytes' 
						AND a.InstanceName = 'systemdiagnosticsperfcounterlibsingleinstance'
						AND a.LoadTestRunId = @RunId
					GROUP BY a.LoadTestRunId, a.MachineName, b.InstanceId
					ORDER BY a.MachineName ASC

				---------------------------------------------------------------------------------
				-- Add a row to the logging table
				---------------------------------------------------------------------------------
				INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (@RunId, 3, GetDate()) 

			FETCH NEXT FROM myCursor INTO @RunId
		END
		CLOSE myCursor
		DEALLOCATE myCursor
	END
	GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_UpdateScoreCardMachineCpuTable
	AS	
	BEGIN
		---------------------------------------------------------------------------------
		-- Executed automatically by VSLT_prc_UpdateLoadTestCoreInfoTable
		---------------------------------------------------------------------------------
		DECLARE @RunId INT
		DECLARE myCursor CURSOR FOR
			SELECT LoadTestRunId FROM LoadTestRun 
			WHERE LoadTestRunId NOT IN (SELECT DISTINCT LoadTestRunId FROM VSLT_ScoreCardMachineCPU)
			AND Outcome != 'In Progress'
			
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @RunId
		WHILE @@FETCH_STATUS = 0
			BEGIN	
				---------------------------------------------------------------------------------
				-- Core Summary Info
				---------------------------------------------------------------------------------
				
				INSERT INTO VSLT_ScoreCardMachineCPU
					SELECT 
						a.LoadTestRunId 
						,a.MachineName
						,0	-- NOTE: Setting MachineType to 'undefined'. Add your own code to determine what type the machine is
						,b.InstanceId
						,COUNT(a.ComputedValue) AS [Qty]
						,CAST(MIN(a.ComputedValue) AS Decimal(15,2)) AS [Min]
						,CAST(AVG(a.ComputedValue) AS Decimal(15,2)) AS [Avg]
						,CAST(MAX(a.ComputedValue) AS Decimal(15,2)) AS [Max]
					FROM LoadTestComputedCounterSample AS a
					INNER JOIN VSLT_View_PerfmonInstanceNamesAndIds AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
						AND a.MachineName = b.MachineName 
						AND a.CategoryName = b.CategoryName
						AND a.CounterName = b.CounterName 
						AND a.InstanceName = b.Instance
					WHERE a.CategoryName = 'Processor' 
						AND a.CounterName = '% Processor Time' 
						AND a.InstanceName = '_Total'
						AND a.LoadTestRunId = @RunId
					GROUP BY a.LoadTestRunId, a.MachineName, b.InstanceId
					ORDER BY a.MachineName ASC

				---------------------------------------------------------------------------------
				-- Add a row to the logging table
				---------------------------------------------------------------------------------
				INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (@RunId, 2, GetDate()) 

			FETCH NEXT FROM myCursor INTO @RunId
		END
		CLOSE myCursor
		DEALLOCATE myCursor
	END
	GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_UpdateCoreInfoWithIntervalFlags
	AS
	BEGIN
		---------------------------------------------------------------------------------
		-- Executed automatically by VSLT_prc_UpdateLoadTestIntervalTable
		---------------------------------------------------------------------------------
	--0:  if interval = 0 and FLAG = 1 THEN Data May not be valid
	--1:  If Interval < 2 and FLAG = 0 THEN use ENTIRE RUN
	--2:  if interval = 1 and FLAG = 1 THEN use INTERVAL VALUES (ssingle iteration)
	--3:  if interval > 1 then NEED TO PROCESS client side

	WITH aggs AS
		(SELECT 
			a.LoadTestRunId
			, COUNT(b.LoadTestRunId) AS Qty
			,a.FLAG_ContainsStepLoadProfile
		FROM VSLT_LoadTestCoreInfo  AS a

		LEFT JOIN VSLT_LoadTestIntervals AS b
			ON a.LoadTestRunId = b.LoadTestRunId
	 
		GROUP BY a.LoadTestRunId, a.FLAG_ContainsStepLoadProfile)

	UPDATE VSLT_LoadTestCoreInfo 
		SET VSLT_LoadTestCoreInfo.FLAG_LoadTestIntervalProfile = a.FLAG_IntervalProfileState
	FROM
		(SELECT 
			aggs.LoadTestRunId
			, (CASE 
					WHEN (aggs.Qty = 0 AND aggs.FLAG_ContainsStepLoadProfile = 1) THEN 0 
					WHEN (aggs.Qty < 2 AND aggs.FLAG_ContainsStepLoadProfile = 0) THEN 1 
					WHEN (aggs.Qty = 1 AND aggs.FLAG_ContainsStepLoadProfile = 1) THEN 2 
					ELSE 3 END) AS FLAG_IntervalProfileState
		FROM aggs) AS a
	WHERE VSLT_LoadTestCoreInfo.LoadTestRunId = a.LoadTestRunId
		AND VSLT_LoadTestCoreInfo.FLAG_LoadTestIntervalProfile IS NULL

	INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (-1, 7, GetDate()) 
END 
	GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_UpdateLoadTestIntervalTable
	AS
	BEGIN
		---------------------------------------------------------------------------------
		-- Executed automatically by VSLT_prc_UpdateLoadTestCoreInfoTable
		---------------------------------------------------------------------------------
		DECLARE @cmptlevel tinyint
		SELECT @cmptlevel = (SELECT cmptlevel from master.dbo.sysdatabases WHERE name = 'master')
		IF(@cmptlevel = 110 OR @cmptlevel = 120) 
		BEGIN

			WITH C1 AS
				(SELECT 
					LoadTestRunId
					, IntervalEndTime
					, ComputedValue
					, CASE 
						WHEN ComputedValue <> coalesce(LAG(ComputedValue) OVER(ORDER BY IntervalEndTime ASC),-1) THEN 1 
						ELSE null END AS IsGroupStart
				FROM LoadTestComputedCounterSample
				WHERE 
					CounterName = 'User Load' 
					AND InstanceName = '_Total')
			,C2 AS
				(SELECT 
					LoadTestRunId
					, IntervalEndTime
					, ComputedValue
					,  COUNT(IsGroupStart) OVER(ORDER BY IntervalEndTime ROWS UNBOUNDED PRECEDING) AS [Group]
				FROM C1)

			INSERT INTO VSLT_LoadTestIntervals (LoadTestRunId, StartOffset, EndOffset, UserLoad, NumberOfDataPoints)
				SELECT 
					C2.LoadTestRunId
					, DATEDIFF(second, run.StartTime, MIN(IntervalEndTime)) AS [IntervalStart]
					, DATEDIFF(second, run.StartTime, MAX(IntervalEndTime)) AS [IntervalEnd]
					, MIN(ComputedValue) AS ComputedValue
					, Count(*) AS NumberOfPoints
				FROM C2
				INNER JOIN LoadTestRun AS run ON C2.LoadTestRunId = run.LoadTestRunId

				WHERE C2.LoadTestRunId NOT IN (SELECT DISTINCT LoadTestRunId FROM VSLT_LoadTestIntervals)
				GROUP BY C2.LoadTestRunId, [Group], run.StartTime
				having count(*) >= 10;

			INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (-1, 6, GetDate()) 
			EXEC VSLT_prc_UpdateCoreInfoWithIntervalFlags
		END
	END
	GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_UpdateLoadTestCoreInfoTable
	AS	
	BEGIN
		---------------------------------------------------------------------------------
		DECLARE @RunId INT
		DECLARE @Qty INT -- value used at the end for parsing validation.
		DECLARE @Duration INT -- value used at the end for parsing validation.
		DECLARE myCursor CURSOR FOR
			SELECT LoadTestRunId FROM LoadTestRun 
			WHERE Outcome != 'InProgress'
			AND LoadTestRunId NOT IN (SELECT LoadTestRunId FROM VSLT_LoadTestCoreInfo)
			
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @RunId
		WHILE @@FETCH_STATUS = 0
			BEGIN	
				---------------------------------------------------------------------------------
				-- Core Summary Info
				---------------------------------------------------------------------------------
				INSERT INTO VSLT_LoadTestCoreInfo (LoadTestRunId)
					SELECT @RunId
				
				-- Set the parsing failed flag to TRUE immediately. Set it to false after all parsing completes
				UPDATE VSLT_LoadTestCoreInfo	SET FLAG_LoadTestParsingFailed = 1 WHERE LoadTestRunId = @RunId

				---------------------------------------------------------------------------------
				-- Agent count and max user count
				---------------------------------------------------------------------------------
				UPDATE VSLT_LoadTestCoreInfo	SET	AgentCount = (SELECT COUNT(AgentId) FROM LoadTestRunAgent WHERE LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
				UPDATE VSLT_LoadTestCoreInfo	SET	MaxUsers = (SELECT TOP 1 ComputedValue FROM LoadTestComputedCounterSample WHERE LoadTestRunId = @RunId 
					AND CategoryName = 'LoadTest:Scenario' AND CounterName = 'User Load' AND InstanceName = '_Total' ORDER BY ComputedValue DESC) WHERE LoadTestRunId = @RunId
				UPDATE VSLT_LoadTestCoreInfo	SET	SampleInterval = (SELECT DATEDIFF(second,IntervalStartTime ,IntervalEndTime) FROM LoadTestRunInterval 
					WHERE TestRunIntervalId = 1 AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
				---------------------------------------------------------------------------------
				-- Test Count and Avg test time
				---------------------------------------------------------------------------------
				BEGIN

					UPDATE VSLT_LoadTestCoreInfo	SET TotalTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Total Tests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET PassedTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Passed Tests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET FailedTests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Failed Tests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET TestsSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Tests/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET AvgTT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Test'
						AND CounterName = 'Avg. Test Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId
				END

				---------------------------------------------------------------------------------
				-- Transactional Count and Avg trans times
				---------------------------------------------------------------------------------
				BEGIN
				
					UPDATE VSLT_LoadTestCoreInfo	SET TotalTrans = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Total Transactions' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET TransSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Transactions/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET AvgTrET_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Avg. Transaction Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET AvgTrRT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Transaction'
						AND CounterName = 'Avg. Response Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

				END

				---------------------------------------------------------------------------------
				-- Page Count and Avg Page times
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE VSLT_LoadTestCoreInfo	SET TotalPages = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
						AND CounterName = 'Total Pages' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET PagesSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
						AND CounterName = 'Pages/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET AvgPgT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Page'
						AND CounterName = 'Avg. Page Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId
				END
				
				---------------------------------------------------------------------------------
				-- Request Count and Avg Request times
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE VSLT_LoadTestCoreInfo	SET TotalRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Total Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET PassedRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Passed Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET FailedRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Failed Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET CachedRequests = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Cached Requests' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET ReqSec_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Requests/Sec' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET AvgReqT_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Avg. Response Time' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET AvgReqLen_All = (SELECT CAST(CumulativeValue AS Decimal(15,2)) FROM LoadTestComputedCounterSummary
						WHERE LoadTestRunId = @RunId AND CategoryName = 'LoadTest:Request'
						AND CounterName = 'Avg. Content Length' AND InstanceName = '_Total') WHERE LoadTestRunId = @RunId
				END

				---------------------------------------------------------------------------------
				-- Test Rig efficiency counters
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE VSLT_LoadTestCoreInfo	SET PctTime_WebTest = (SELECT CAST(AVG(ComputedValue) AS Decimal(15,2)) FROM LoadTestComputedCounterSample	
						WHERE CategoryName = 'LoadTest:Test' AND CounterName = '% Time in WebTest code' AND InstanceName = '_Total' 
						AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
							
					UPDATE VSLT_LoadTestCoreInfo	SET PctTime_Rules = (SELECT CAST(AVG(ComputedValue) AS Decimal(15,2)) FROM LoadTestComputedCounterSample	
						WHERE CategoryName = 'LoadTest:Test' AND CounterName = '% Time in Rules' AND InstanceName = '_Total' 
						AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
							
					UPDATE VSLT_LoadTestCoreInfo	SET PctTime_LoadPlugin = (SELECT CAST(AVG(ComputedValue) AS Decimal(15,2)) FROM LoadTestComputedCounterSample	
						WHERE CategoryName = 'LoadTest:Test' AND CounterName = '% Time in LoadTestPlugin' AND InstanceName = '_Total' 
						AND LoadTestRunId = @RunId) WHERE LoadTestRunId = @RunId
				END			

				---------------------------------------------------------------------------------
				-- Flags
				---------------------------------------------------------------------------------
				BEGIN
					UPDATE VSLT_LoadTestCoreInfo	SET FLAG_ContainsPages = (CASE (SELECT COUNT(PageId) FROM LoadTestPageSummaryData WHERE LoadTestRunId = @RunId)
							WHEN 0 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					UPDATE VSLT_LoadTestCoreInfo	SET FLAG_ContainsTransactions = (CASE (SELECT COUNT(TransactionId) FROM LoadTestTransactionSummaryData  WHERE LoadTestRunId = @RunId)
							WHEN 0 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					UPDATE VSLT_LoadTestCoreInfo	SET FLAG_ContainsDetailedTimingInfo = (CASE (SELECT COUNT(TestDetailId) FROM LoadTestTestDetail WHERE LoadTestRunId = @RunId)
							WHEN 0 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					UPDATE VSLT_LoadTestCoreInfo	SET FLAG_TestAborted = (CASE (SELECT Outcome FROM LoadTestRun WHERE LoadTestRunId = @RunId)
							WHEN 'Completed' THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId

					UPDATE VSLT_LoadTestCoreInfo	SET FLAG_ContainsStepLoadProfile = (CASE 
								(SELECT COUNT(DISTINCT ComputedValue) FROM LoadTestPerformanceCounterSample AS a
								INNER JOIN VSLT_View_PerfmonInstanceNamesAndIds AS b
								ON a.LoadTestRunId = b.LoadTestRunId
								AND a.InstanceId = b.InstanceId
								WHERE a.LoadTestRunId = @RunId AND b.CounterName = 'User Load' 
								AND b.Instance = '_Total' AND a.ComputedValue <> 0)
							WHEN 1 THEN 0 ELSE 1 END) WHERE LoadTestRunId = @RunId
			
					-- Parsing completed for the run, so do a final sanity check on the results, looking for a total test Qty > 0 OR a Duration > 0.
					-- If either are zero or NULL, then the results will be invalid. If not, then we're good to go.
					SET @Qty = (SELECT TotalTests FROM VSLT_LoadTestCoreInfo WHERE LoadTestRunId = @RunId)
					SET @Duration = (SELECT RunDuration FROM LoadTestRun WHERE LoadTestRunId = @RunId)
					IF (@Qty IS NOT NULL AND @Qty > 0 AND @Duration > 0) 
					BEGIN
						UPDATE VSLT_LoadTestCoreInfo	SET FLAG_LoadTestParsingFailed = (0) WHERE LoadTestRunId = @RunId
					END

				END

				---------------------------------------------------------------------------------
				-- Add a row to the logging table
				---------------------------------------------------------------------------------
				INSERT INTO TSL_DbChangeLogs (LoadTestRunId, ChangeTypeId, ChangeTime) VALUES (@RunId, 1, GetDate()) 

			FETCH NEXT FROM myCursor INTO @RunId
		END
		CLOSE myCursor
		DEALLOCATE myCursor

		EXEC VSLT_prc_UpdateScoreCardMachineAvailableMBTable
		EXEC VSLT_prc_UpdateScoreCardMachineCpuTable
		EXEC VSLT_prc_UpdateLoadTestIntervalTable
	END
	GO



/*===============================================================================
Id	Page Name	Qty		Min		Avg		Max		StDev	90%		95%		Scenario		TestName
1	http://name	400		0.01	0.05	0.65	0.08	0.17	0.24	FLOW_MS_TESTS	000_LOGIN
3	http://name	2000	0.01	0.05	0.73	0.09	0.16	0.24	FLOW_MS_TESTS	000_LOGIN
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Page_ResponseTimes
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = NULL
		,@NumberOfRows INT = 25
	AS
	BEGIN
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)


		DECLARE @Results TABLE ([id] int, [Name] NVARCHAR(255), [Total] INT,
			[Minimum] DECIMAL(15,2),[Average] DECIMAL(15,2), [Maximum] DECIMAL(15,2), 
			[Std Dev] DECIMAL(15,2), [90Percent] DECIMAL(15,2),	[95Percent] DECIMAL(15,2),
			[Scenario] NVARCHAR(255), [TestName] NVARCHAR(255))

		INSERT INTO @Results 
		([id],[Name],[Total],[Minimum],[Average],[Maximum],[Std Dev])
			SELECT
				c.PageId
				,d.RequestUri
				,COUNT(DISTINCT (c.PageDetailId))
				,CAST(MIN(c.ResponseTime) AS Decimal(15,2))
				,CAST(AVG(c.ResponseTime) AS Decimal(15,2))
				,CAST(MAX(c.ResponseTime) AS Decimal(15,2))
				,CAST(STDEV(c.ResponseTime) AS Decimal(15,2))
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
		
			INNER JOIN dbo.LoadTestPageDetail AS c 
				ON a.LoadTestRunId = c.LoadTestRunId 
				AND a.TestDetailId = c.TestDetailId
				AND c.Outcome = 0

			INNER JOIN dbo.WebLoadTestRequestMap AS d
				ON a.LoadTestRunId = d.LoadTestRunId
				AND c.PageId = d.RequestId
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND (@NameFilter IS NULL OR d.RequestUri LIKE @NameFilter)
				AND (c.Outcome = 0) 
				AND (a.InMeasurementInterval = 1) 
			GROUP BY d.RequestUri, c.PageId

		UPDATE @Results SET 
			[90Percent] = (select CAST(MIN(ResponseTime) AS Decimal(15,2)) from 
				(select top 10 percent ResponseTime 
					from LoadTestPageDetail 
					where LoadTestRunId = @RunId 
					AND PageId=[id]
					AND (Outcome = 0) 
					AND (InMeasurementInterval = 1) 
					AND 
						(@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
						AND
						(EndTime < DATEADD(second, @EndOffset, @StartTime))))
					order by ResponseTime desc) as LTD),
			[95Percent] = (select CAST(MIN(ResponseTime) AS Decimal(15,2)) from 
				(select top 5 percent ResponseTime 
					from LoadTestPageDetail 
					where LoadTestRunId = @RunId 
					AND PageId=[id]
					AND (Outcome = 0) 
					AND (InMeasurementInterval = 1) 
					AND 
						(@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
						AND
						(EndTime < DATEADD(second, @EndOffset, @StartTime))))
					order by ResponseTime desc) as LTD),
			[Scenario] = (SELECT ScenarioName FROM VSLT_View_PageNamesWithIDs WHERE LoadTestRunId = @RunId AND RequestId = [id]),
			[TestName] = (SELECT TestCaseName FROM VSLT_View_PageNamesWithIDs WHERE LoadTestRunId = @RunId AND RequestId = [id])
		FROM @Results AS r

		SELECT TOP (@NumberOfRows) [Id]
			,[Name] AS 'Page Name'
			,[Total] AS Qty
			,[Minimum] AS 'Min'
			,[Average] AS 'Avg'
			,[Maximum] AS 'Max'
			,[Std Dev] AS 'StDev'
			,[90Percent] AS '90%'
			,[95Percent] AS '95%'
			,[Scenario]
			,[TestName] 
		FROM @Results ORDER BY [Id] ASC
	END
    GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Page_Throughput
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
	AS
	BEGIN
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

		DECLARE @Duration Decimal
		SET @Duration = CASE WHEN (@StartOffset IS NULL) 
			THEN (SELECT RunDuration FROM LoadTestRun WHERE LoadTestRunId = @RunId) 
			ELSE @EndOffset - @StartOffset END


		;WITH Aggs AS
		(
			SELECT
				c.PageId AS [PageId]
				,d.RequestUri AS [Name]
				,COUNT(DISTINCT (c.PageDetailId)) AS [Qty]
				--,CAST(AVG( COUNT(DISTINCT (c.PageDetailId)) / @Duration) AS Decimal(15,2)) AS [Throughput]
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
		
			INNER JOIN dbo.LoadTestPageDetail AS c 
				ON a.LoadTestRunId = c.LoadTestRunId 
				AND a.TestDetailId = c.TestDetailId
				AND c.Outcome = 0

			INNER JOIN dbo.WebLoadTestRequestMap AS d
				ON a.LoadTestRunId = d.LoadTestRunId
				AND c.PageId = d.RequestId
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND d.RequestUri LIKE @NameFilter
				AND (c.Outcome = 0) 
				AND (a.InMeasurementInterval = 1) 
			GROUP BY d.RequestUri, c.PageId
		)

		SELECT
			[PageId]
			,[Name]
			,[Qty]
			,@Duration AS [runDuration]
			,CAST([Qty] / @Duration AS Decimal(15,2)) AS [Throughput]
		FROM Aggs
	END
    GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Page_PercentMeetingThreshold
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
		,@ThresholdValue Decimal = 1.0

	AS
	BEGIN
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

		;WITH Counts AS
		(
			SELECT
				c1.PageId AS [PageId]
				,d.RequestUri AS [Name]
				,COUNT(DISTINCT (c1.PageDetailId)) AS [TotalQty]
				,COUNT(DISTINCT (c2.PageDetailId)) AS [MeetingGoal]
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
		
			INNER JOIN dbo.LoadTestPageDetail AS c1 
				ON a.LoadTestRunId = c1.LoadTestRunId 
				AND a.TestDetailId = c1.TestDetailId
				AND c1.Outcome = 0

			LEFT OUTER JOIN dbo.LoadTestPageDetail AS c2 
				ON a.LoadTestRunId = c2.LoadTestRunId 
				AND c1.PageDetailId = c2.PageDetailId
				AND c2.ResponseTime <= @ThresholdValue

			INNER JOIN dbo.WebLoadTestRequestMap AS d
				ON a.LoadTestRunId = d.LoadTestRunId
				AND c1.PageId = d.RequestId
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR c1.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR c1.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND d.RequestUri LIKE @NameFilter
				AND (c1.Outcome = 0) 
				AND (a.InMeasurementInterval = 1) 
			GROUP BY d.RequestUri, c1.PageId
		)

		SELECT [PageId], [Name], [TotalQty], [MeetingGoal]
			,CAST(([MeetingGoal] / CAST([TotalQty] AS Decimal)) * 100 AS Decimal(15,0)) AS [Percent Meeting Goal]
		FROM Counts
	END
	GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Page_ResponseTimes_GroupedByName
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
		,@ExcludeNameFilter nvarchar(255) = NULL
	AS
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

			DECLARE @Results TABLE ([Id] INT, [Name] NVARCHAR(255), [Total] INT,
				[Min] DECIMAL(15,2),[Avg] DECIMAL(15,2), [Max] DECIMAL(15,2), 
				[StDev] DECIMAL(15,2), [90%] DECIMAL(15,2),	[95%] DECIMAL(15,2))
	
			INSERT INTO @Results 
			([Id],[Name],[Total],[Min],[Avg],[Max],[StDev])
			SELECT
				0  -- added to keep symmetry between the tables
				,d.RequestUri
				,COUNT(DISTINCT (c.PageDetailId)) AS [Total]
				,CAST(MIN(c2.ResponseTime) AS Decimal(15,2))
				,CAST(AVG(c2.ResponseTime) AS Decimal(15,2))
				,CAST(MAX(c2.ResponseTime) AS Decimal(15,2))
				,CAST(STDEV(c2.ResponseTime) AS Decimal(15,2))
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
			INNER JOIN dbo.LoadTestPageDetail AS c 
				ON a.LoadTestRunId = c.LoadTestRunId 
				AND a.TestDetailId = c.TestDetailId

			LEFT OUTER JOIN LoadTestPageDetail AS c2
				ON c.LoadTestRunId = c2.LoadTestRunId
				AND c.PageDetailId = c2.PageDetailId

			INNER JOIN dbo.WebLoadTestRequestMap AS d
				ON a.LoadTestRunId = d.LoadTestRunId
				AND c.PageId = d.RequestId
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND (@ExcludeNameFilter IS NULL OR d.RequestUri NOT LIKE @ExcludeNameFilter)
				AND d.RequestUri LIKE @NameFilter
				AND (c2.Outcome = 0) 
				AND (c2.InMeasurementInterval = 1) 
			GROUP BY d.RequestUri


			UPDATE @Results SET 
			[90%] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
				FROM (
					SELECT top 10 percent c.ResponseTime, d.RequestUri 
					FROM LoadTestPageDetail AS c
					INNER JOIN dbo.WebLoadTestRequestMap AS d
						ON c.LoadTestRunId = d.LoadTestRunId
						AND c.PageId = d.RequestId
					WHERE c.LoadTestRunId = @RunId
						AND RequestUri = r.Name
						AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
							AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
					AND (Outcome = 0) 
					AND (InMeasurementInterval = 1) 
					ORDER BY ResponseTime DESC
					) as LTD
				)
			,[95%] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
				FROM (
					SELECT top 5 percent c.ResponseTime, d.RequestUri 
					FROM LoadTestPageDetail AS c
					INNER JOIN dbo.WebLoadTestRequestMap AS d
						ON c.LoadTestRunId = d.LoadTestRunId
						AND c.PageId = d.RequestId
					WHERE c.LoadTestRunId = @RunId
						AND RequestUri = r.Name
						AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
							AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
					AND (Outcome = 0) 
					AND (InMeasurementInterval = 1) 
					ORDER BY ResponseTime DESC
					) as LTD
				)
		FROM @Results AS r

		SELECT TOP (@NumberOfRows) [Id],[Name],[Total] AS Qty,[Min],[Avg],[Max],[StDev], [90%], [95%] 
		FROM @Results ORDER BY [Avg] DESC
	GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Test_ResponseTimes
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
	AS
	BEGIN
	DECLARE @StartTime DATETIME
	SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

		DECLARE @Results TABLE ([id] int, [Name] NVARCHAR(255), [Total] INT,
			[Minimum] DECIMAL(15,2),[Average] DECIMAL(15,2), [Maximum] DECIMAL(15,2), 
			[Std Dev] DECIMAL(15,2), [90Percent] DECIMAL(15,2),	[95Percent] DECIMAL(15,2),
			[Scenario] NVARCHAR(255), [TestName] NVARCHAR(255))

		INSERT INTO @Results 
		([id],[Name], [Total], [Minimum],[Average],[Maximum],[Std Dev], [TestName])
			SELECT
				a.TestCaseId   -- added to keep the table shape identical to the transactionbyinterval sproc
				,c.TestCaseName
				,COUNT(DISTINCT (a.TestDetailId)) AS [Total]
				,CAST(MIN(a.ElapsedTime) AS Decimal(15,2)) AS [Min]
				,CAST(AVG(a.ElapsedTime) AS Decimal(15,2)) AS [Avg]
				,CAST(MAX(a.ElapsedTime) AS Decimal(15,2)) AS [Max]
				,CAST(STDEV(a.ElapsedTime) AS Decimal(15,2)) AS [Std Dev]
				,'-'
			FROM 
				dbo.LoadTestTestDetail AS a
			INNER JOIN 
				LoadTestRun AS b 
				ON a.LoadTestRunId = b.LoadTestRunId
			INNER JOIN
				dbo.LoadTestCase AS c
				ON a.LoadTestRunId = c.LoadTestRunId
				AND a.TestCaseId = c.TestCaseId		
			WHERE 
				a.LoadTestRunId = @RunId
				AND (@StartOffset IS NULL OR a.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
				AND (@EndOffset IS NULL OR a.EndTime < DATEADD(second, @EndOffset, b.StartTime))
				AND (a.Outcome = 10) 
				AND c.TestCaseName LIKE @NameFilter
				AND (a.InMeasurementInterval = 1) 
			GROUP BY c.TestCaseName, a.TestCaseId

		UPDATE @Results SET 
			[90Percent] = (SELECT CAST(MIN(LTD.ElapsedTime) AS Decimal(15,2)) 
							FROM (select top 10 percent ElapsedTime
									from LoadTestTestDetail 
									where LoadTestRunId = @RunId
									AND TestCaseId = r.Id
									AND (Outcome = 10) 
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
											AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (InMeasurementInterval = 1) 
									order by ElapsedTime desc) as LTD),
			[95Percent] = (SELECT CAST(MIN(LTD.ElapsedTime) AS Decimal(15,2)) 
							FROM (select top 5 percent ElapsedTime
									from LoadTestTestDetail 
									where LoadTestRunId = @RunId
									AND TestCaseId = r.Id
									AND (Outcome = 10) 
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
											AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (InMeasurementInterval = 1) 
									order by ElapsedTime desc) as LTD),
			[Scenario] = (SELECT ScenarioName FROM VSLT_View_TestNamesWithIDs WHERE LoadTestRunId = @RunId AND TestCaseId = [id])
		FROM @Results r
					
		SELECT TOP (@NumberOfRows) [Id]
			,[Name] AS 'Test Name'
			,[Total] AS Qty
			,[Minimum] AS 'Min'
			,[Average] AS 'Avg'
			,[Maximum] AS 'Max'
			,[Std Dev] AS 'StDev'
			,[90Percent] AS '90%'
			,[95Percent] AS '95%'
			,[Scenario]
		FROM @Results ORDER BY [Id] ASC
	END
    GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Transaction_ResponseTimes
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
	AS
	BEGIN
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

			DECLARE @Results TABLE ([id] INT, [Name] NVARCHAR(255), [Total] INT,
				[Minimum] DECIMAL(15,2),[Average] DECIMAL(15,2), [Maximum] DECIMAL(15,2), 
				[Std Dev] DECIMAL(15,2), [90Percent] DECIMAL(15,2),	[95Percent] DECIMAL(15,2),
				[Scenario] NVARCHAR(255), [TestName] NVARCHAR(255))
	
			INSERT INTO @Results 
			([id],[Name],[Total],[Minimum],[Average],[Maximum],[Std Dev])
				SELECT
					c.TransactionId
					,d.TransactionName
					,COUNT(DISTINCT (c.TransactionDetailId)) AS [Total]
					,CAST(MIN(c.ResponseTime) AS Decimal(15,2)) AS [Min]
					,CAST(AVG(c.ResponseTime) AS Decimal(15,2)) AS [Avg]
					,CAST(MAX(c.ResponseTime) AS Decimal(15,2)) AS [Max]
					,CAST(STDEV(c.ResponseTime) AS Decimal(15,2)) AS [Std Dev]
				FROM 
					dbo.LoadTestTestDetail AS a
				INNER JOIN 
					LoadTestRun AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
				INNER JOIN dbo.LoadTestTransactionDetail AS c 
					ON a.LoadTestRunId = c.LoadTestRunId 
					AND a.TestDetailId = c.TestDetailId
				INNER JOIN dbo.WebLoadTestTransaction AS d
					ON a.LoadTestRunId = d.LoadTestRunId
					AND c.TransactionId = d.TransactionId
				WHERE 
					a.LoadTestRunId = @RunId
					AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
					AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
					AND d.TransactionName LIKE @NameFilter
					AND (a.InMeasurementInterval = 1) 
				GROUP BY d.TransactionName, c.TransactionId

			UPDATE @Results SET 
			[90Percent] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
							FROM (select top 10 percent ResponseTime
									from LoadTestTransactionDetail 
									where LoadTestRunId = @RunId
									AND TransactionId = r.Id
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
										AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (InMeasurementInterval = 1) 
									order by ResponseTime desc) as LTD),
			[95Percent] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
							FROM (select top 5 percent ResponseTime
									from LoadTestTransactionDetail 
									where LoadTestRunId = @RunId
									AND TransactionId = r.Id
									AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
										AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
									AND (InMeasurementInterval = 1) 
									order by ResponseTime desc) as LTD),
			[Scenario] = (SELECT ScenarioName FROM VSLT_View_TransactionNamesWithIDs WHERE LoadTestRunId = @RunId AND TransactionId = [id]),
			[TestName] = (SELECT TestCaseName FROM VSLT_View_TransactionNamesWithIDs WHERE LoadTestRunId = @RunId AND TransactionId = [id])
		FROM @Results AS r
		
		SELECT TOP (@NumberOfRows) [Id]
			,[Name] AS 'Transaction Name'
			,[Total] AS Qty
			,[Minimum] AS 'Min'
			,[Average] AS 'Avg'
			,[Maximum] AS 'Max'
			,[Std Dev] AS 'StDev'
			,[90Percent] AS '90%'
			,[95Percent] AS '95%'
			,[Scenario]
			,[TestName] 
		FROM @Results ORDER BY [Id] ASC
	END
    GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Transaction_ResponseTimes_GroupedByName
		@RunId  INT
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
		,@NameFilter nvarchar(255) = '%'
		,@NumberOfRows INT = 25
	AS

		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

			DECLARE @Results TABLE ([Id] INT, [Name] NVARCHAR(255), [Total] INT,
				[Min] DECIMAL(15,2),[Avg] DECIMAL(15,2), [Max] DECIMAL(15,2), 
				[StDev] DECIMAL(15,2), [90%] DECIMAL(15,2),	[95%] DECIMAL(15,2))
	
			INSERT INTO @Results 
			([Id], [Name],[Total],[Min],[Avg],[Max],[StDev])
				SELECT
					0   -- Added to keep the table symmetry with other procs
					,d.TransactionName
					,COUNT(DISTINCT (c.TransactionDetailId)) AS [Total]
					,CAST(MIN(c.ResponseTime) AS Decimal(15,2)) AS [Min]
					,CAST(AVG(c.ResponseTime) AS Decimal(15,2)) AS [Avg]
					,CAST(MAX(c.ResponseTime) AS Decimal(15,2)) AS [Max]
					,CAST(STDEV(c.ResponseTime) AS Decimal(15,2)) AS [Std Dev]
				FROM 
					dbo.LoadTestTestDetail AS a
				INNER JOIN 
					LoadTestRun AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
				INNER JOIN dbo.LoadTestTransactionDetail AS c 
					ON a.LoadTestRunId = c.LoadTestRunId 
					AND a.TestDetailId = c.TestDetailId
				INNER JOIN dbo.WebLoadTestTransaction AS d
					ON a.LoadTestRunId = d.LoadTestRunId
					AND c.TransactionId = d.TransactionId
				WHERE 
					a.LoadTestRunId = @RunId
					AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
					AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
					AND d.TransactionName LIKE @NameFilter
				GROUP BY d.TransactionName

			UPDATE @Results SET 
			[90%] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
				FROM (
					SELECT top 10 percent c.ResponseTime, d.TransactionName 
					FROM LoadTestTransactionDetail AS c
					INNER JOIN dbo.WebLoadTestTransaction AS d
						ON c.LoadTestRunId = d.LoadTestRunId
						AND c.TransactionId = d.TransactionId
					WHERE c.LoadTestRunId = @RunId
						AND TransactionName = r.Name
						AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
							AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
					ORDER BY ResponseTime DESC
					) as LTD
				)
			,[95%] = (SELECT CAST(MIN(LTD.ResponseTime) AS Decimal(15,2)) 
				FROM (
					SELECT top 5 percent c.ResponseTime, d.TransactionName 
					FROM LoadTestTransactionDetail AS c
					INNER JOIN dbo.WebLoadTestTransaction AS d
						ON c.LoadTestRunId = d.LoadTestRunId
						AND c.TransactionId = d.TransactionId
					WHERE c.LoadTestRunId = @RunId
						AND TransactionName = r.Name
						AND (@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
							AND (EndTime < DATEADD(second, @EndOffset, @StartTime))))
					ORDER BY ResponseTime DESC
					) as LTD
				)
		FROM @Results AS r
		
		SELECT TOP (@NumberOfRows) [Id],[Name],[Total] AS Qty,[Min],[Avg],[Max],[StDev], [90%], [95%] 
		FROM @Results ORDER BY [Name] DESC
	GO



/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Perfmon_CounterValuesWithIntervals
		@RunId  INT
		,@Machine NVARCHAR(255) = NULL
		,@Category NVARCHAR(255) = 'Processor'
		,@Counter NVARCHAR(255) = '% Processor Time'
		,@Instance NVARCHAR(255) = '_Total'
		,@StartOffset INT = NULL
		,@EndOffset INT = NULL
	AS
	BEGIN	
		DECLARE @StartTime DATETIME
		SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

		SELECT
			c.InstanceId AS 'Id'
			,c.InstanceName AS 'Name'
			,CAST(MIN(ComputedValue) AS Decimal(15,2)) AS 'Min'
			,CAST(AVG(ComputedValue) AS Decimal(15,2)) AS 'Avg'
			,CAST(MAX(ComputedValue) AS Decimal(15,2)) AS 'Max'
			,CAST(STDEV(ComputedValue) AS Decimal(15,2)) AS 'StDev'
		FROM [dbo].[LoadTestPerformanceCounterSample] AS a

		INNER JOIN LoadTestRunInterval AS b
			ON a.LoadTestRunId = b.LoadTestRunId
			AND a.TestRunIntervalId = b.TestRunIntervalId

		INNER JOIN VSLT_View_PerfmonInstanceNamesAndIds AS c
			ON a.LoadTestRunId = c.LoadTestRunId
			AND a.InstanceId = c.InstanceId

		WHERE a.LoadTestRunId = @RunId
		AND (c.CategoryName = @Category AND c.CounterName = @Counter AND c.Instance = @Instance)
		AND (@Machine IS NULL OR c.MachineName LIKE @Machine)
		AND (@StartOffset IS NULL OR IntervalEndTime > DATEADD(second, @StartOffset, @StartTime))
		AND (@EndOffset IS NULL OR IntervalEndTime < DATEADD(second, @EndOffset, @StartTime))
		GROUP BY c.InstanceId, c.InstanceName

	END
    GO
/*===============================================================================
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_GetAllPageIntervalTimings
	 @Run int
	 ,@Name nvarchar(255) = ''
	 AS
	 BEGIN
		DECLARE myCursor CURSOR FOR
			SELECT StartOffset, EndOffset, UserLoad FROM VSLT_LoadTestIntervals WHERE LoadTestRunId = @Run

		DECLARE @Start int
		DECLARE @End int
		DECLARE @UserCount int
			
		OPEN myCursor
		FETCH NEXT FROM myCursor INTO @Start, @End, @UserCount
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			DECLARE @SQL nvarchar(max)
			SELECT @SQL = N'
			DECLARE 
				@RunId INT = ' + CAST(@Run AS nvarchar(25)) + ',
				@StartOffset INT = ' + CAST(@Start AS nvarchar(25)) + ',
				@EndOffset INT = ' + CAST(@End AS nvarchar(25)) + ',
				@NameFilter nvarchar(255)= N''' + @Name + ''',
				@UserCount INT = ' + CAST(@UserCount AS nvarchar(25)) + ',
				@NumberOfRows INT = 25
			DECLARE @StartTime DATETIME
			SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)


			DECLARE @Results TABLE ([id] int, [Name] NVARCHAR(255), [Total] INT,
				[Minimum] DECIMAL(15,2),[Average] DECIMAL(15,2), [Maximum] DECIMAL(15,2), 
				[Std Dev] DECIMAL(15,2), [90Percent] DECIMAL(15,2),	[95Percent] DECIMAL(15,2),
				[Scenario] NVARCHAR(255), [TestName] NVARCHAR(255))

			INSERT INTO @Results 
			([id],[Name],[Total],[Minimum],[Average],[Maximum],[Std Dev])
				SELECT
					c.PageId
					,d.RequestUri
					,COUNT(DISTINCT (c.PageDetailId))
					,CAST(MIN(c.ResponseTime) AS Decimal(15,2))
					,CAST(AVG(c.ResponseTime) AS Decimal(15,2))
					,CAST(MAX(c.ResponseTime) AS Decimal(15,2))
					,CAST(STDEV(c.ResponseTime) AS Decimal(15,2))
				FROM 
					dbo.LoadTestTestDetail AS a
				INNER JOIN 
					LoadTestRun AS b 
					ON a.LoadTestRunId = b.LoadTestRunId
		
				INNER JOIN dbo.LoadTestPageDetail AS c 
					ON a.LoadTestRunId = c.LoadTestRunId 
					AND a.TestDetailId = c.TestDetailId
					AND c.Outcome = 0

				INNER JOIN dbo.WebLoadTestRequestMap AS d
					ON a.LoadTestRunId = d.LoadTestRunId
					AND c.PageId = d.RequestId
				WHERE 
					a.LoadTestRunId = @RunId
					AND (@StartOffset IS NULL OR c.EndTime >= DATEADD(second, @StartOffset, b.StartTime))
					AND (@EndOffset IS NULL OR c.EndTime < DATEADD(second, @EndOffset, b.StartTime))
					AND (@NameFilter = '''' OR d.RequestUri LIKE @NameFilter)
					AND (c.Outcome = 0) 
					AND (a.InMeasurementInterval = 1) 
				GROUP BY d.RequestUri, c.PageId

			UPDATE @Results SET 
				[90Percent] = (select CAST(MIN(ResponseTime) AS Decimal(15,2)) from 
					(select top 10 percent ResponseTime 
						from LoadTestPageDetail 
						where LoadTestRunId = @RunId 
						AND PageId=[id]
						AND (Outcome = 0) 
						AND (InMeasurementInterval = 1) 
						AND 
							(@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
							AND
							(EndTime < DATEADD(second, @EndOffset, @StartTime))))
						order by ResponseTime desc) as LTD),
				[95Percent] = (select CAST(MIN(ResponseTime) AS Decimal(15,2)) from 
					(select top 5 percent ResponseTime 
						from LoadTestPageDetail 
						where LoadTestRunId = @RunId 
						AND PageId=[id]
						AND (Outcome = 0) 
						AND (InMeasurementInterval = 1) 
						AND 
							(@StartOffset IS NULL OR ((EndTime > DATEADD(second, @StartOffset, @StartTime))
							AND
							(EndTime < DATEADD(second, @EndOffset, @StartTime))))
						order by ResponseTime desc) as LTD),
				[Scenario] = (SELECT ScenarioName FROM VSLT_View_PageNamesWithIDs WHERE LoadTestRunId = @RunId AND RequestId = [id]),
				[TestName] = (SELECT TestCaseName FROM VSLT_View_PageNamesWithIDs WHERE LoadTestRunId = @RunId AND RequestId = [id])
			FROM @Results AS r

			SELECT TOP (@NumberOfRows) [Id]
				,[Name] AS [Page Name]
				,[Total] AS Qty
				,[Minimum] AS [Min]
				,[Average] AS [Avg]
				,[Maximum] AS [Max]
				,[Std Dev] AS [StDev]
				,[90Percent] AS [90%]
				,[95Percent] AS [95%]
				,[Scenario]
				,[TestName]
				,@UserCount AS [User Count]
				,@StartOffset AS [Start Offset] 
				,@EndOffset AS [End Offset] 
			FROM @Results ORDER BY [Id] ASC
		'
			--print @SQL
			--Execute the generated query
			exec sp_executesql @SQL
			FETCH NEXT FROM myCursor INTO @Start, @End, @UserCount
		END
		CLOSE myCursor
		DEALLOCATE myCursor
	 END

		--START CODE--
		GRANT EXECUTE ON VSLT_prc_UpdateLoadTestCoreInfoTable TO PUBLIC
	GO
/*===============================================================================
EXEC	VSLT_prc_Page_Multirun_Comparison
		@RunIds = N'[21],[22],[23],[24]'
		,@ComparisonColumn = 'Percentile90'

NOTE1: @RunIds is a single string with a comma separated list of runs and each run being 
	surrounded by square brackets.
NOTE2: @ComparisonColumn is the name of any of the summary columns from the VSLT_View_PageSummary
	view. If this parameter is not sent, the sproc defaults to the 'Average' column.
---------------------------------------------------------------------------------------------
ScenarioName	TestCaseName	RequestUri					21		22		23		24
Scenario1		TestCase01		http://url.com/page1.aspx	0.17	0.16	0.11	0.11
Scenario1		TestCase02		http://url.com/page2.aspx	0.08	0.04	0.03	0.02
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Page_Multirun_Comparison
		@RunIds nvarchar(1024)
		,@ComparisonColumn nvarchar(25) = 'Average'
	AS
	BEGIN
		DECLARE @RunIdsStripped nvarchar(1024)
		SET @RunIdsStripped = (SELECT REPLACE(REPLACE(@RunIds, '[', ''), ']', ''))

		DECLARE @SQL nvarchar(max)
		SELECT @SQL = N'
			select * from 
			(
					SELECT 
					ScenarioName
					,TestCaseName
					,LoadTestRunId
					,RequestUri
					,CAST(' + @ComparisonColumn + ' AS Decimal(15,2)) AS ' + @ComparisonColumn + '
					FROM VSLT_View_PageSummary
					WHERE LoadTestRunId IN ('+ @RunIdsStripped + ')
			) as SourceTable
			PIVOT
			(
			MAX(' + @ComparisonColumn + ') 
				FOR LoadTestRunID in ('+ @RunIds + ')
			) as PivotTable
			ORDER BY RequestUri ASC'
			-- print @SQL
			-- Execute the generated query
			exec sp_executesql @SQL

			--
	END
	GO
/*===============================================================================
EXEC	VSLT_prc_Page_Multirun_Comparison
		@RunIds = N'[21],[22],[23],[24]'

NOTE1: @RunIds is a single string with a comma separated list of runs and each run being 
	surrounded by square brackets.
---------------------------------------------------------------------------------------------
ScenarioName	TestCaseName	RequestUri					21		22		23		24
Scenario1		TestCase01		http://url.com/page1.aspx	0.17	0.16	0.11	0.11
Scenario1		TestCase02		http://url.com/page2.aspx	0.08	0.04	0.03	0.02
*/
	--START CODE--
	CREATE PROCEDURE VSLT_prc_Page_Throughput_Multirun_Comparison
		@RunIds nvarchar(1024)
	AS
	BEGIN
		DECLARE @RunIdsStripped nvarchar(1024)
		SET @RunIdsStripped = (SELECT REPLACE(REPLACE(@RunIds, '[', ''), ']', ''))

		DECLARE @SQL nvarchar(max)
		SELECT @SQL = N'
			select * from 
			(
					SELECT 
					ScenarioName
					,TestCaseName
					,LoadTestRunId
					,RequestUri
					,CAST(((CAST(PageCount AS Decimal))/ RunDuration) AS Decimal(15,2)) AS Throughput
					FROM VSLT_View_PageSummary
					WHERE LoadTestRunId IN ('+ @RunIdsStripped + ')
			) as SourceTable
			PIVOT
			(
			MAX(Throughput) 
				FOR LoadTestRunID in ('+ @RunIds + ')
			) as PivotTable
			ORDER BY RequestUri ASC'
			print @SQL
			-- Execute the generated query
			exec sp_executesql @SQL
	END
	GO
/*== VSLT_prc_UserLoadProfile ====================================================
* Gets the number of users at each sampling interval of the test run. Used for calculating constant 
* load intervals from Step Load Test Runs.
* EXAMPLE:
EXEC  VSLT_prc_UserLoadProfile
            @RunId = 9
===============================================================================*/
      --START CODE--
	CREATE PROCEDURE VSLT_prc_UserLoadProfile
            @RunId int
      AS
      BEGIN
            DECLARE @StartTime DATETIME
            SET @StartTime = (SELECT StartTime FROM LoadTestRun WHERE LoadTestRunId = @RunId)

            SELECT 
                  ComputedValue AS UserCount
                  ,DATEDIFF(second, @StartTime, IntervalEndTime) AS [Interval]
            FROM 
                  LoadTestComputedCounterSample
            WHERE 
                  LoadTestRunId = @RunId
                  AND CategoryName = 'LoadTest:Scenario'
                  AND CounterName = 'User Load'
                  AND InstanceName = '_Total'
            ORDER BY [Interval] ASC


      END
	GO




/*== Prc_DeleteLoadTestRun ==================================================================================
* Updates the Prc_DeleteLoadTestRun SPROC so that it deletes data from the TSL tables whenever
* Visual Studio deletes data from the main DB tables
===============================================================================*/
	--START CODE--
	ALTER PROCEDURE Prc_DeleteLoadTestRun 
		@LoadTestRunId int
	AS
	BEGIN

		-- First delete the data collector log tables
		EXEC Prc_DeleteDataCollectorLogs @LoadTestRunId

		----------------------------------------------------------------
		-- TempTable to handle the tables to use for delete.
		-- rownum will insure we retrieve in the right order
		-- so foreign key constraints don't bite us
		DECLARE @Temp TABLE
			  (rownum int
			   , tableToUse nvarchar(50))

		-- Build out temp table, 
		INSERT INTO @temp VALUES(1, 'LoadTestFileAttachmentChunk')
		INSERT INTO @temp VALUES(2, 'LoadTestFileAttachment')
		INSERT INTO @temp VALUES(3, 'LoadTestDataCollectorLog')
		INSERT INTO @temp VALUES(4, 'LoadTestTestLog')
		INSERT INTO @temp VALUES(5, 'LoadTestBrowsers')
		INSERT INTO @temp VALUES(6, 'LoadTestNetworks')
		INSERT INTO @temp VALUES(7, 'LoadTestDetailMessage')
		INSERT INTO @temp VALUES(8, 'LoadTestTestDetail')
		INSERT INTO @temp VALUES(9, 'LoadTestPageDetail')
		INSERT INTO @temp VALUES(10, 'LoadTestTransactionDetail')
		INSERT INTO @temp VALUES(11, 'LoadTestTestSummaryData')
		INSERT INTO @temp VALUES(12, 'LoadTestTransactionSummaryData')
		INSERT INTO @temp VALUES(13, 'LoadTestPageSummaryData')
		INSERT INTO @temp VALUES(14, 'LoadTestPageSummaryByNetwork')
		INSERT INTO @temp VALUES(15, 'LoadTestCase')
		INSERT INTO @temp VALUES(16, 'LoadTestMessage')
		INSERT INTO @temp VALUES(17, 'LoadTestMessageType')
		INSERT INTO @temp VALUES(18, 'LoadTestThresholdMessage')
		INSERT INTO @temp VALUES(19, 'LoadTestPerformanceCounter')
		INSERT INTO @temp VALUES(20, 'LoadTestPerformanceCounterCategory')
		INSERT INTO @temp VALUES(21, 'LoadTestPerformanceCounterInstance')
		INSERT INTO @temp VALUES(22, 'LoadTestPerformanceCounterSample')
		INSERT INTO @temp VALUES(23, 'LoadTestRunAgent')
		INSERT INTO @temp VALUES(24, 'LoadTestRunInterval')
		INSERT INTO @temp VALUES(25, 'LoadTestScenario')
		INSERT INTO @temp VALUES(26, 'LoadTestSqlTrace')
		INSERT INTO @temp VALUES(27, 'WebLoadTestErrorDetail')
		INSERT INTO @temp VALUES(28, 'WebLoadTestTransaction')
		INSERT INTO @temp VALUES(29, 'WebLoadTestRequestMap')
		INSERT INTO @temp VALUES(30, 'LoadTestSystemUnderTestTag')
		INSERT INTO @temp VALUES(31, 'LoadTestSystemUnderTest')
		INSERT INTO @temp VALUES(32, 'LoadTestRun')

		----------------------------------------------------------------
		-- Added by the VSLT Reporting Tool
		INSERT INTO @temp VALUES(33, 'VSLT_LoadTestCoreInfo')
		INSERT INTO @temp VALUES(34, 'VSLT_ScoreCardMachineAvailableMB')
		INSERT INTO @temp VALUES(35, 'VSLT_ScoreCardMachineCPU')
		----------------------------------------------------------------


		----------------------------------------------------------------
		-- Variables to control the behavior of the query
		DECLARE @iEndOfTable int
		DECLARE @iRowsToDelete int
		DECLARE @iRownum int
		DECLARE @tableName nvarchar(50)
		DECLARE @QueryToUse nvarchar(500)

		set @iEndOfTable = 0
		SET @iRowsToDelete = 10000
		SET @iRownum = 1


		WHILE @iRownum < 33
		BEGIN
			  SELECT @tableName = tableToUse FROM @TEMP WHERE rownum = @iRownum
			  WHILE @iEndOfTable = 0
			  BEGIN
					SET @QueryToUse = 'DELETE TOP(' + CAST(@iRowsToDelete AS nvarchar(10)) + ') FROM ' +
								@tableName + ' WHERE LoadTestRunId = ' +  CAST(@LoadTestRunId AS nvarchar(10))
					EXECUTE sp_executesql @QueryToUse
					IF (@@rowcount < @iRowsToDelete)
						  SET @iEndOfTable = 1
			  END
			  SET @iRownum = @iRownum + 1
			  SET @iEndOfTable = 0
		END

		----------------------------------------------------------------
		-- Added by the VSLT Reporting Tool
		WHILE @iRownum < 36
		BEGIN
			  SELECT @tableName = tableToUse FROM @TEMP WHERE rownum = @iRownum
			  SET @QueryToUse = 'DELETE FROM ' + @tableName + ' WHERE LoadTestRunId = ' 
					+  CAST(@LoadTestRunId AS nvarchar(10))
			  EXECUTE sp_executesql @QueryToUse
			  SET @iRownum = @iRownum + 1			
		END
		----------------------------------------------------------------
	
	END	
	GO	
/*== Prc_UpdateSummaryData3 =================================================================================
* Updates the Prc_UpdateSummaryData3 SPROC so that it adds the summary data from the main tables whenever
* Visual Studio finishes writing results to the DB at the end of a test run.
===============================================================================*/
	--START CODE--
	ALTER PROCEDURE Prc_UpdateSummaryData3 
		@LoadTestRunId int
		, @DeleteDetailTables bit
	AS
	BEGIN
		INSERT INTO LoadTestTestSummaryData 
			(LoadTestRunId, TestCaseId, TestsRun, Average, Minimum, Maximum, StandardDeviation)
		   SELECT LoadTestRunId, TestCaseId,
				  count(*) as TestsRun,
				  avg(ElapsedTime) as Average, 
				  min(ElapsedTime) as Minimum,
				  max(ElapsedTime) as Maximum,
				  ISNULL(STDEVP(ElapsedTime),0) AS StandardDeviation
			FROM  LoadTestTestDetail 
			WHERE LoadTestRunId=@LoadTestRunId
				  AND InMeasurementInterval = 1
			GROUP BY LoadTestRunId, TestCaseId

			EXEC Prc_UpdateTestSummary3 @LoadTestRunId


		INSERT INTO LoadTestTransactionSummaryData 
			(LoadTestRunId, TransactionId, TransactionCount, Average, Minimum, Maximum,AvgTransactionTime, StandardDeviation)
			SELECT LoadTestRunId, TransactionId,
				   count(*) as TransactionCount,
				   avg(ResponseTime) as Average, 
				   min(ResponseTime) as Minimum,
					   max(ResponseTime) as Maximum,
					   avg(ElapsedTime) as AverageTransactionTime,
					   ISNULL(STDEVP(ResponseTime),0) AS StandardDeviation
			FROM LoadTestTransactionDetail 
			WHERE  LoadTestRunId=@LoadTestRunId
				   AND InMeasurementInterval = 1
			GROUP BY LoadTestRunId, TransactionId

			EXEC Prc_UpdateTransactionSummary3 @LoadTestRunId


		INSERT INTO LoadTestPageSummaryData 
			(LoadTestRunId, PageId, PageCount, Average, Minimum, Maximum, StandardDeviation)
			SELECT LoadTestRunId, PageId,
				   count(*) as PageCount,
				   avg(ResponseTime) as Average, 
				   min(ResponseTime) as Minimum,
				   max(ResponseTime) as Maximum,
					   ISNULL(STDEVP(ResponseTime),0) AS StandardDeviation
			FROM   LoadTestPageDetail 
			WHERE  LoadTestRunId=@LoadTestRunId
				   AND InMeasurementInterval = 1
			GROUP BY LoadTestRunId, PageId

			EXEC Prc_UpdatePageSummary3 @LoadTestRunId


		INSERT INTO LoadTestPageSummaryByNetwork
			(LoadTestRunId, PageId, NetworkId, PageCount, Average, Minimum, Maximum, Goal, StandardDeviation)
			SELECT pageDetail.LoadTestRunId, pageDetail.PageId, testDetail.NetworkId,
				   count(*) as PageCount,
				   avg(ResponseTime) as Average, 
				   min(ResponseTime) as Minimum,
				   max(ResponseTime) as Maximum,
				   avg(ResponseTimeGoal) as Goal,
					   ISNULL(STDEVP(ResponseTime),0) AS StandardDeviation
			FROM   LoadTestPageDetail as pageDetail
			INNER JOIN LoadTestTestDetail as testDetail
			ON pageDetail.LoadTestRunId = testDetail.LoadTestRunId
				  AND pageDetail.TestDetailId = testDetail.TestDetailId
			WHERE pageDetail.LoadTestRunId = @LoadTestRunId
				  AND pageDetail.InMeasurementInterval = 1
			GROUP BY pageDetail.LoadTestRunId, PageId, testDetail.NetworkId
        
			EXEC Prc_UpdatePageSummaryByNetwork3 @LoadTestRunId

		IF @DeleteDetailTables = 1
		BEGIN
			DELETE from LoadTestTestDetail where LoadTestRunId = @LoadTestRunId
			DELETE from LoadTestTransactionDetail where LoadTestRunId = @LoadTestRunId
			DELETE from LoadTestPageDetail where LoadTestRunId = @LoadTestRunId
		END

			----------------------------------------------------------------
			-- Added by the VSLT Reporting Tool
			EXEC VSLT_prc_UpdateLoadTestCoreInfoTable
			EXEC VSLT_prc_UpdateScoreCardMachineAvailableMBTable
			EXEC VSLT_prc_UpdateScoreCardMachineCpuTable
			----------------------------------------------------------------
	END
	GO
/*== Add The LogChange Codes to the DB ====================================================================*/
	--START CODE--
	INSERT INTO TSL_DbChangeLogTypes ([ChangeTypeId] ,[ChangeTypeDescription])
		 VALUES  (1, 'VSLT_prc_UpdateLoadTestCoreInfoTable')
				,(2, 'VSLT_prc_UpdateScoreCardMachineCpuTable')
				,(3, 'VSLT_prc_UpdateScoreCardMachineAvailableMBTable')
				,(4, 'VSLT_prc_GetStartingValuesForImportingPerfmon')
				,(5, 'VSLT_prc_UpdatePerfmonTablesFromImport')
				,(6, 'VSLT_prc_UpdateLoadTestIntervalTable')
				,(7, 'VSLT_prc_UpdateLoadTestCoreInfoTable_IntervalProfileFlag')
	GO
/*== Add The ProfileInterval Codes to the DB ==============================================================*/
	--START CODE--
	INSERT INTO VSLT_IntervalProfileTypes ([ProfileTypeId] ,[ProfileTypeDescription])
		 VALUES  (0, 'Data May not be valid')
				,(1, 'use ENTIRE RUN')
				,(2, 'use INTERVAL VALUES (only one interval exists)')
				,(3, 'Indeterminant, multiple intervals exist')
	GO
/*== Add The MachineType Codes to the DB ==================================================================*/
	--START CODE--
	INSERT INTO VSLT_ScoreCardMachineType ([MachineTypeId] ,[MachineTypeDescription])
		 VALUES  (0, 'Undefined')
				,(1, 'Web')
				,(2, 'App')
				,(3, 'Database')
				,(4, 'Gateway')
				,(5, 'BizTalk')
				,(6, 'SharePoint')
				,(3, 'Fileshare')
	GO



	--START CODE--
	GRANT EXECUTE ON VSLT_prc_UpdateScoreCardMachineAvailableMBTable TO PUBLIC
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_UpdateScoreCardMachineCpuTable TO PUBLIC
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_UpdateLoadTestIntervalTable TO PUBLIC 
	GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_UpdateCoreInfoWithIntervalFlags TO PUBLIC
    GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_Page_Multirun_Comparison TO PUBLIC 
	GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_Page_Throughput_Multirun_Comparison TO PUBLIC 
	GO
	--START CODE--
    GRANT EXECUTE ON VSLT_prc_Page_PercentMeetingThreshold TO PUBLIC
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_Page_Throughput TO PUBLIC
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_Page_ResponseTimes TO PUBLIC
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_Page_ResponseTimes_GroupedByName TO PUBLIC
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_Perfmon_CounterValuesWithIntervals TO PUBLIC
    GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_Transaction_ResponseTimes_GroupedByName TO PUBLIC	
	GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_Transaction_ResponseTimes TO PUBLIC
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_Test_ResponseTimes TO PUBLIC
    GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_GetAllPageIntervalTimings TO PUBLIC
    GO
	--START CODE--
	GRANT EXECUTE ON VSLT_prc_Get_TSL_Reporting_Version TO PUBLIC
	GO
	--START CODE--
	GRANT EXECUTE ON Prc_DeleteLoadTestRun TO PUBLIC
	GO
	--START CODE--
	GRANT EXECUTE ON Prc_UpdateSummaryData3 TO PUBLIC
	GO
    --START CODE--
    GRANT EXECUTE ON VSLT_prc_UserLoadProfile TO PUBLIC
    GO



